//1502
package cn.ecnu.auction.dao;

/**
 * @author lychen
 * date 20120501
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import ToolPackage.Json;

import cn.ecnu.auction.db.DbConnection;
import cn.ecnu.auction.po.User;

/**
 * @author MyEasy
 * 
 */
public class UserDAO {
	private static Logger logger = Logger.getLogger(UserDAO.class);

	/**
	 * 本函数根据传入的用户名模糊查询user表中含有该名字的用户
	 * @param userName 模糊用户名
	 * @return ArrayList<User> 用户数组。如果没有，返回null
	 */
	public String getUserByUserName(String userName) {
		Connection conn = DbConnection.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		ArrayList<User> users = new ArrayList<User>();
		try {
			stmt = conn.createStatement();
			sql = "select userId, userName, userPassword, userStatus from t_user where username like '%"
					+ userName
					+ "%' or userLoginName like '%"
					+ userName
					+ "%'";
			logger.info(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				User user = fillUserObject(rs);
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		if (users.size() > 0) {
			return Json.list2json(users);
		}
		return null;
	}

	/**
	 * 计算符合查询条件的数据的数量
	 * @param year
	 * @param provinceName
	 * @return
	 */
	public int countNumByUserName(String userName) {
		int num = 0;
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) as num from t_user where username like '%"
				+ userName + "%' or userLoginName like '%" + userName + "%'";
		try {
			pstmt = conn.prepareStatement(sql);
			logger.info(pstmt);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt("num");
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		return num;
	}

	private User fillUserObject(ResultSet rs) throws SQLException {
		User user = new User();
		user.setUserId(rs.getInt("userId"));
		user.setUserName(rs.getString("userName"));
		user.setUserPassword(rs.getString("userPassword"));
		user.setMobile(rs.getString("mobile"));
		user.setEmail(rs.getString("email"));
		return user;
	}

	/**
	 * 返回t_user表中用户的个数�?
	 * 
	 * @return 用户的个�?result = -1 表示异常 result >=0 表示实际个数
	 */
	public int getUserCount() {
		Connection conn = DbConnection.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		int result = 0;
		try {
			stmt = conn.createStatement();
			sql = "select count(1) as userCount from t_user";
			logger.info(sql);
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				result = rs.getInt("userCount");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}

		return result;
	}

	/**
	 * 返回当前页的�?��用户对象
	 * @param start �?��行数
	 * @param end 结束行数
	 * @return ArrayList<User> 如果没有,返回null
	 * @throws SQLException
	 */
	public ArrayList<User> getPagedUser(int start, int end) {
		Connection conn = DbConnection.getConnection();
		ArrayList<User> users = new ArrayList<User>();
		String sql = "select * from (select rownum as rowNumber, userId, userLoginName, userName, userPassword, userStatus from t_user order by userName asc) t1 where t1.rowNumber between ? and ?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, end);
			logger.info(pstmt);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				users.add(fillUserObject(rs));
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		if (users.size() > 0) {
			return users;
		}
		return null;
	}

	/**
	 * 增加新用户
	 * @param userName 用户真实姓名
	 * @param userPassword 用户密码
	 * @param mobile 联系电话
	 * @param email 邮箱
	 * @return result >= 0 正确  result = -1 错误
	 */
	public int addUser(String userName,String userPassword,String mobile,String email){
		Connection conn = DbConnection.getConnection();
		String sql = "insert into user(userId, userName, userPassword, mobile, email) values(null, ?, ?, ?, ?)";
		int result = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);
			pstmt.setString(2, userPassword);
			pstmt.setString(3, mobile);
			pstmt.setString(4, email);
			logger.info(pstmt);
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}
		return result;
	}

	/**
	 * 修改用户信息
	 * @param userId 用户Id
	 * @param userName 用户真实姓名
	 * @param userPassword 用户密码
	 * @param mobile 联系电话
	 * @param email 邮箱
	 * @return result >= 0 正确 result = -1 表示错误
	 */
	public int editUser(int userId, String userPassword, String mobile, String email) {
		Connection conn = DbConnection.getConnection();
		String sql = "update user set userPassword = ?, mobile = ?, email = ? where userId = ?";
		int result = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userPassword);
			pstmt.setString(2, mobile);
			pstmt.setString(3, email);
			pstmt.setInt(4, userId);
			logger.info(pstmt);
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}
		return result;
	}

	/**
	 * 禁用�?��用户
	 * 
	 * @param userId
	 *            用户Id
	 * @return result >= 0 正确 result = -1 表示错误
	 */
	public int disableUser(int userId) {
		Connection conn = DbConnection.getConnection();
		String sql = "update t_user set userStatus = -1 where userId = ?";
		int result = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			logger.info(pstmt);
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}
		return result;
	}

	/**
	 * 启用�?��用户
	 * 
	 * @param userId
	 *            用户Id
	 * @return result >= 0 正确 result = -1 表示错误
	 */
	public int enableUser(int userId) {
		Connection conn = DbConnection.getConnection();
		String sql = "update t_user set userStatus = 0 where userId = ?";
		int result = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			logger.info(pstmt);
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}
		return result;
	}

	/**
	 * 判断用户可以登录
	 * @param userLoginName 用户登录名
	 * @param userPassword 用户密码
	 * @return result >=0 可以登录 result = -1 不可以登录
	 */
	public int checkUserLogin(String userLoginName, String userPassword) {
		Connection conn = DbConnection.getConnection();
		String sql = "select userPassword from user where userStatus = 0 and userLoginName = ?";
		int result = -1;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userLoginName);
			logger.info(pstmt);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				if (userPassword.equals(rs.getString("userPassword"))) {
					result = 0;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}
		return result;
	}

	/**
	 * 查询全部用户信息
	 * 
	 * @return jsonString
	 */
	public String getAllUser(int pageNum, int recordNum, int userId) {
		int startNum = (pageNum - 1) * recordNum;
		int endNum = pageNum * recordNum;
		String whereString = "where userId <> 1";
		if (userId == 1) {
			whereString = "";
		}
		Connection conn = DbConnection.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		ArrayList<User> users = new ArrayList<User>();
		try {
			stmt = conn.createStatement();
			sql = "select * from (select rowNum as no, userId, userLoginName, userName, userPassword, userStatus from t_user "
					+ whereString
					+ " order by userId) where no > "
					+ startNum
					+ " and no <= " + endNum;
			logger.info(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				User user = fillUserObject(rs);
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		if (users.size() > 0) {
			return Json.list2json(users);
		}
		return null;
	}

	/**
	 * 计算符合查询条件的数据的数量
	 * 
	 * @author Cynric
	 * @param year
	 * @param provinceName
	 * @return
	 */
	public int countNum() {
		int num = 0;
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) as num from t_user where userId <> 1";
		try {
			pstmt = conn.prepareStatement(sql);
			logger.info(pstmt);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt("num");
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		return num;
	}

	/**
	 * 获取密码
	 * 
	 * @author Saxon date 20120901
	 * @param userLoginName
	 * @return ArrayList
	 */
	public ArrayList<User> getQueryUserPassword(String userLoginName) {
		String sql = "select userId, userLoginName, userName, userPassword, userStatus from t_user where userLoginName=?";
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<User> list = new ArrayList<User>();
		try {
			logger.info(sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userLoginName);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				User user = fillUserObject(rs);
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		if (list.size() > 0) {
			return list;
		}
		return null;
	}

	/**
	 * 删除用户信息
	 * 
	 * @param userIdString
	 * @return
	 */
	public int getDeleteUserData(String userIdString) {
		String userSql = "delete from t_user where userId in (" + userIdString
				+ ")";
		String userRoleSql = "delete from t_user_role where userId in ("
				+ userIdString + ")";
		PreparedStatement pstmtUser = null;
		PreparedStatement pstmtUserRole = null;
		int result = 0;
		Connection conn = DbConnection.getConnection();
		try {
			conn.setAutoCommit(false);
			pstmtUserRole = conn.prepareStatement(userRoleSql);
			logger.info(pstmtUserRole);
			result = pstmtUserRole.executeUpdate();
			pstmtUser = conn.prepareStatement(userSql);
			logger.info(pstmtUser);
			result = pstmtUser.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			logger.error(e);
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}
		return result;
	}
}
